﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using CH.Easy.DbProvider.ExpressionExtend;
using System.Collections.ObjectModel;
using System.Data.Common;
using CH.Easy.DbProvider.Common;
using System.Text.RegularExpressions;

namespace CH.Easy.DbProvider.Core
{
    public abstract class ExpressionTSQLTranslator : DbExpressionVisitor
    {
        protected StringBuilder sb;
        protected List<DbParameter> parameter;
        protected Dictionary<string, string> tableAlias;
        protected Expression expression;
        protected int tableIndex = 0;
        protected int paramIndex = 0;
        private void Reset()
        {
            this.sb = new StringBuilder();
            this.parameter = new List<DbParameter>();
            this.tableAlias = new Dictionary<string, string>();
            this.expression = null;
            this.tableIndex = 0;
            this.paramIndex = 0;
        }
        public virtual string Translator(Expression expression)
        {
            this.Reset();
            this.expression = expression;
            this.Visit(this.expression);
            string sql = this.sb.ToString();
            foreach (var item in this.tableAlias)
            {
                sql = new Regex(item.Key).Replace(sql, item.Value);
            }
            return sql;
        }
        /// <summary>
        /// 获取自增ID Sql
        /// </summary>
        /// <returns></returns>
        public abstract string GetIdentitySQL();
        #region Append

        protected virtual void Append(object obj)
        {
            this.sb.Append(obj.ToString());
        }
        #endregion
        #region Result
        public virtual DbParameter[] GetParameter()
        {
            return this.parameter.ToArray();
        }
      
        #endregion
        #region VisitCustomExpression
        protected override Expression VisitSelectExpression(SelectExpression se)
        {



            this.Visit(se.Take);
            this.Visit(se.Column);
            this.Visit(se.From);
            this.Visit(se.InnerJoin);
            this.Visit(se.Where);
            this.Visit(se.Skip);
            this.Visit(se.Group);
            this.Visit(se.Order);
            return se;
        }
        protected override Expression VisitDeleteExpression(RemoveExpression deleteExpression)
        {


            
            this.Append("DELETE ");
            this.Append(deleteExpression.Name);
            this.Append(" FROM " + deleteExpression.Name + " AS " + deleteExpression.Alias);
            if (deleteExpression.Expression != null)
            {
                this.Append(" WHERE ");
                this.Visit(deleteExpression.Expression);
                if (!tableAlias.ContainsKey(deleteExpression.Alias))
                {
                    tableAlias.Add(deleteExpression.Alias, "t" + tableIndex++);
                }
            }
            
            return deleteExpression.Expression;
        }
        protected override Expression VisitInsertExpression(AddExpression insertExpression)
        {

            if (insertExpression.ChangeValue == null && insertExpression.ChangeValue.Count == 0)
            {
                return insertExpression;
            }
            
            this.Append("INSERT INTO ");
            this.Append(insertExpression.Name);
            this.Append("(");
            List<ConstantExpression> list = new List<ConstantExpression>();
            int index = 0;
            int count = insertExpression.ChangeValue.Count();
            foreach (var item in insertExpression.ChangeValue)
            {
                this.Append("[" + item.Key + "]");
                list.Add(Expression.Constant(item.Value));
                if (index++ != count - 1)
                {
                    this.Append(",");
                }
            }
            this.Append(") VALUES (");
            for (int i = 0; i < list.Count; i++)
            {
                this.Visit(list[i]);
                if (i != list.Count() - 1)
                {
                    this.Append(",");
                }
            }


            this.Append(")");

            if (insertExpression.IsIdentity)
            {
                this.Append(this.GetIdentitySQL());
            }



            return insertExpression;
        }
        protected override Expression VisitModifyExpression(ModifyExpression updateExpression)
        {

            if (updateExpression.ChangeValue == null || updateExpression.ChangeValue.Count == 0)
            {
                return updateExpression;
            }
         
            this.Append("UPDATE ");
            this.Append(updateExpression.Name);
            this.Append(" SET ");
            int index = 0;
            int count = updateExpression.ChangeValue.Count();
            foreach (var item in updateExpression.ChangeValue)
            {
                this.Append("[" + item.Key + "] = ");
                this.Visit(Expression.Constant(item.Value));
                if (index++ != count - 1)
                {
                    this.Append(",");
                }
            }
            this.Append(" FROM " + updateExpression.Name + " AS " + updateExpression.Alias);

            if (updateExpression.Expression != null)
            {
                this.Append(" WHERE ");
                this.Visit(updateExpression.Expression);
                if (!tableAlias.ContainsKey(updateExpression.Alias))
                {
                    tableAlias.Add(updateExpression.Alias, "t" + tableIndex++);
                }
            }
            


            return updateExpression.Expression;
        }
        protected override Expression VisitColumnExpression(ColumnExpression columnExpression)
        {



            if (columnExpression.Expression != null)
            {
                if (columnExpression.Expression.NodeType == ExpressionType.Call)
                {
                   
                    //处理聚合函数类型 例如
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Count();
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Sum(p => p.ID);
                    MethodCallExpression methodCallExpression = columnExpression.Expression as MethodCallExpression;
                    this.Visit(methodCallExpression);
                }
                else
                {
                    
                    //处理多个列
                    LambdaExpression lambda = (LambdaExpression)StripQuotes(columnExpression.Expression);
                  
                    if (lambda.Body.NodeType == ExpressionType.MemberInit)
                    {
                        MemberInitExpression mie = lambda.Body as MemberInitExpression;
                        if (mie.Bindings.Count > 0)
                        {
                            //Select(p => new MyHome() { ID = p.ID, Name = p.Name })
                            this.Visit(lambda.Body);
                        }
                        else
                        {
                            //Select(p => new MyHome())
                            this.Append(" * ");
                        }
                    }
                    else if (lambda.Body.NodeType == ExpressionType.MemberAccess || lambda.Body.NodeType == ExpressionType.New)
                    {
                        //Select(p => p.Name)
                        //Select(p => new { id = p.ID, name = p.Name })
                        //Select(p => new { id = p.Key.ID, name = p.Key.Name.Max() })
                        this.Visit(lambda.Body);
                    }
                    else if (lambda.Body.NodeType == ExpressionType.Parameter)
                    {
                        //Select(p => p)
                        this.Append(" * ");
                    }
                    else
                    {
                        //格式错误的时候 默认 *
                        this.Append(" * ");
                    }
                }


            }
            else
            {
                this.Append(" * ");
            }


            return columnExpression;
        }
        protected override Expression VisitGroupExpression(GroupExpression groupExpression)
        {
            if (groupExpression.Expression != null)
            {
                LambdaExpression lambda = (LambdaExpression)StripQuotes(groupExpression.Expression);
                if (lambda.Body.NodeType == ExpressionType.MemberInit)
                {
                    MemberInitExpression mie = lambda.Body as MemberInitExpression;
                    if (mie.Bindings.Count > 0)
                    {
                        this.Append(" GROUP BY ");
                        this.Visit(lambda.Body);
                    }
                }
            }

            return groupExpression;
        }
        protected override Expression VisitWhereExpression(WhereExpression whereExpression)
        {
            if (whereExpression.Expression != null)
            {
                this.Append(" WHERE ");
                this.Visit(whereExpression.Expression);
            }
            return whereExpression;
        }
        protected override Expression VisitTakeExpression(TakeExpression takeExpression)
        {
            if (takeExpression.Expression != null)
            {

                ConstantExpression ce = takeExpression.Expression as ConstantExpression;
                if (ce != null)
                {
                    this.Append("SELECT TOP ");
                    this.Append(ce.Value);
                    this.Append(" ");
                }
            }
            else
            {
                this.Append("SELECT ");
            }
            return takeExpression;
        }
        protected override Expression VisitOrderExpression(OrderExpression orderExpression)
        {

            if (orderExpression.OrderCollection.Count > 0)
            {

                this.Append(" ORDER BY ");
                for (int i = 0; i < orderExpression.OrderCollection.Count; i++)
                {
                    //方法 Asc(ID)
                    MethodCallExpression mce = orderExpression.OrderCollection[i] as MethodCallExpression;
                    this.Visit(mce);
                    if (i != orderExpression.OrderCollection.Count - 1)
                    {
                        //最后一个元素 不加分隔符逗号
                        //Order By ID ASC,NAME DESC
                        this.Append(",");
                    }
                }

            }
            return orderExpression;
        }
        protected override Expression VisitSkipExpression(SkipExpression skipExpression)
        {

            if (skipExpression.Expression != null)
            {
                ConstantExpression ce = skipExpression.Expression as ConstantExpression;
                SelectExpression se = this.expression as SelectExpression;


                if (ce != null)
                {

                    if (se.IsHaveWhere)
                    {
                        this.Append(" AND ");
                    }
                    else
                    {
                        this.Append(" WHERE ");
                    }
                    this.Append("(");
                    this.Append(se.From.Alias + "." + se.From.PrimaryKey);
                    this.Append(" NOT IN(SELECT TOP ");
                    this.Append(ce.Value);
                    this.Append(" " + se.From.Alias + "." + se.From.PrimaryKey);
                    this.Visit(se.From);
                    this.Visit(se.Where);
                    this.Visit(se.Group);
                    this.Visit(se.Order);
                    this.Append(")) ");
                }
            }
            return skipExpression;
        }
        protected override Expression VisitFromExpression(FromExpression fromExpression)
        {
            if (fromExpression.Select != null)
            {

                if (fromExpression.Select.Expression.NodeType == ExpressionType.Constant)
                {
                   
                    this.Append(" FROM ");
                    this.Append(fromExpression.Name);
                    this.Append(" AS ");
                    this.Append(fromExpression.Alias);

                }
                else
                {
                    this.Append(" FROM ");
                    this.Append("(");
                    this.Visit(fromExpression.Select);
                    this.Append(") AS ");
                    this.Append(fromExpression.Alias);

                }

                if (!tableAlias.ContainsKey(fromExpression.Alias))
                {
                    tableAlias.Add(fromExpression.Alias, "t" + tableIndex++);
                }
            }
            return fromExpression;
        }
        protected override Expression VisitInnerJoinExpression(InnerJoinExpression innerJoinExpression)
        {
            if (innerJoinExpression.Expression != null)
            {
                this.Append(" INNER JOIN ");
                this.Visit(innerJoinExpression.Inner);
                this.Append(" ON ");
                this.Visit(innerJoinExpression.OutKey);
                this.Append("=");
                this.Visit(innerJoinExpression.InnerKey);
                this.Append(" ");
            }


            return innerJoinExpression;
        }

        #endregion
        #region VisitExpression
        protected override Expression VisitMethodCall(MethodCallExpression m)
        {
            if (m.Method.Name == "Count")
            {
                //输出 COUNT
                this.Append(m.Method.Name.ToUpper());
                //输出(ID)
                this.Append("(");

                if (m.Arguments[0].NodeType == ExpressionType.MemberAccess)
                {
                    this.Visit(m.Arguments[0]);
                }
                else if (m.Arguments[0].NodeType == ExpressionType.Constant)
                {
                    ConstantExpression ce = m.Arguments[0] as ConstantExpression;
                    this.Append(ce.Value);
                }
                else
                {
                    this.Append(1);
                }

                this.Append(")");
            }
            else if (m.Method.Name == "Sum" || m.Method.Name == "Max" || m.Method.Name == "Min")
            {
                this.Append(m.Method.Name.ToUpper());
                this.Append("(CAST(");
                this.Visit(m.Arguments[1]);
                this.Append(" AS BIGINT))");
            }
            else if (m.Method.Name == "Average")
            {
                this.Append("AVG");
                this.Append("(CAST(");
                this.Visit(m.Arguments[1]);
                this.Append(" AS BIGINT))");
            }
            else if (m.Method.Name == "Like")
            {
                //(p => p.Email.Like("%t%")) Where 条件
                //输出 Email
                this.Append("(");
                this.Visit(m.Arguments[0]);
                //输出 Like 
                this.Append(" " + m.Method.Name + " ");
                this.Visit(m.Arguments[1]);
                this.Append(")");

            }
            else if (m.Method.Name == "In" || m.Method.Name == "NotIn")
            {
                int[] array = null;
                string arrayStr = string.Empty;
                ConstantExpression ce = null;
                MemberExpression me = m.Arguments[1] as MemberExpression;
                if (me != null)
                {
                    //处理这种方式
                    //int[] array = new int []{ 1, 2, 4, 5, 6 };
                    //exp.And(p => p.ID.In(array)); 这种方式会生成一个匿名类来封装array 所以只能使用反射的方式获取值
                    ce = (ConstantExpression)me.Expression;
                    array = ce.Type.GetFields().First().GetValue(ce.Value) as int[];
                }
                else
                {
                    //处理这种方式 exp.And(p => p.ID.In(new int[] { 1, 2, 4, 5, 6 }));
                    NewArrayExpression nae = m.Arguments[1] as NewArrayExpression;
                    array = nae.Expressions.Select(p => (int)(((ConstantExpression)p).Value)).ToArray();
                }

                this.Append("(");
                this.Visit(m.Arguments[0]);
                if (m.Method.Name == "In")
                {
                    this.Append(" IN ");
                }
                else
                {
                    this.Append(" NOT IN ");
                }
                this.Append("(");

                for (int i = 0; i < array.Length; i++)
                {
                    arrayStr += array[i];
                    if (i < array.Length - 1)
                    {
                        arrayStr += ",";
                    }
                }

                this.Append(arrayStr);
                this.Append("))");

            }
            else if (m.Method.Name == "As")
            {
                //处理Select(p => new MyHome() { ID = p.Key.ID.Count().As("ID"), Email = p.Key.Email.Max().As("Email") })
                //输出 COUNT(ID)
                this.Visit(m.Arguments[0]);
                //输出 AS ID
                if (m.Arguments.Count > 1)
                {
                    ConstantExpression ce = m.Arguments[1] as ConstantExpression;
                    this.Append(" AS " + ce.Value + " ");
                }

            }
            else if (m.Method.Name == "Order")
            {
                LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[0]);
                if (lambda.Body.NodeType == ExpressionType.MemberAccess)
                {
                    //输出 ID
                    this.Visit(m.Arguments[0]);
                    //输出 ASC
                    this.Visit(m.Arguments[1]);
                }
                else if (lambda.Body.NodeType == ExpressionType.Call)
                {
                    //输出 ID
                    MethodCallExpression methodCallExpression = lambda.Body as MethodCallExpression;
                    this.Append("[" + (methodCallExpression.Arguments[1] as ConstantExpression).Value + "]");
                    //输出 ASC
                    this.Visit(m.Arguments[1]);
                }

            }
            else if (m.Method.Name == "ToString")
            {
                //忽略ToString 方法
                this.Visit(m.Object);
            }

            return m;
        }
        protected override Expression VisitBinary(BinaryExpression b)
        {
            this.Append("(");
            this.Visit(b.Left);
            switch (b.NodeType)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    this.Append(" AND ");
                    break;
                case ExpressionType.Or:
                    this.Append(" OR");
                    break;
                case ExpressionType.Equal:
                    this.Append(" = ");
                    break;
                case ExpressionType.NotEqual:
                    this.Append(" <> ");
                    break;
                case ExpressionType.LessThan:
                    this.Append(" < ");
                    break;
                case ExpressionType.LessThanOrEqual:
                    this.Append(" <= ");
                    break;
                case ExpressionType.GreaterThan:
                    this.Append(" > ");
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    this.Append(" >= ");
                    break;
                default:
                    throw new NotSupportedException(string.Format("不支持操作符{0}", b.NodeType));
            }


            if (b.Right.NodeType == ExpressionType.MemberAccess)
            {
                var memberExp = (b.Right as MemberExpression);
                var value = Expression.Lambda(memberExp).Compile().DynamicInvoke();
                ConstantExpression ce = Expression.Constant(value);
                this.Visit(ce);
            }
            else
            {
                this.Visit(b.Right);
            }
            this.Append(")");
            return b;
        }
        protected override Expression VisitParameter(ParameterExpression p)
        {
            this.Append(" " + p.Name + " ");
            return p;
        }
        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            this.Append("T" + m.Expression.Type.GetHashCode() + ".[" + m.Member.Name + "]");
            return m;
        }
        protected override IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
        {

            for (int i = 0; i < original.Count; i++)
            {
                MemberBinding b = this.VisitBinding(original[i]);
                if (i != original.Count - 1)
                {
                    this.Append(",");
                }
            }
            return original;
        }
        protected override ReadOnlyCollection<Expression> VisitExpressionList(ReadOnlyCollection<Expression> original)
        {
            for (int i = 0; i < original.Count; i++)
            {
                Expression p = this.Visit(original[i]);
                if (i != original.Count - 1)
                {
                    this.Append(",");
                }
            }

            return original;
        }
        #endregion
    }
}
